﻿using System;
using System.Data;
using System.Collections;
using System.Collections.Specialized;
using WP.DB;
using WP.RM.DataEntity;
using WP.Commons;

namespace WP.RM.DataAccess
{
    public class RMWorkQtyInputHdrDAL  : IDataAccess
    {
        public RMWorkQtyInputHdrDAL()
		{
		}
        #region Select List
        public string SQLList(int iParameter)
        {
            string strSQL = @"SELECT RecID,WorkShopSeqNo,WorkShopName,WorkDate,WorkGroupSeqNo,WorkGroupName,WorkingProcedureID,WorkingProcedureName,ProductFItemID,ProductID,ProductName,Unit,WorkQty,CalType,Remark FROM rmWorkQtyInputHdr Where Post =0 order by ProductFItemID,WorkingProcedureName";
            return strSQL;
        }
        #endregion

        #region Fields
        public string Fields
        {
            get
            {
                return @"  WorkShopSeqNo,WorkDate,InputDate,WorkGroupSeqNo,WorkingProcedureID,WorkingProcedureName,
                           ProductFItemID,ProductID,ProductName,Unit,WorkQty,CalType,WorkGroupName,WorkShopName,Remark,
                           SemiCode,SemiName,SemiRecID,MachineSeqNo,MachineName
                        ";
            }
        }
        #endregion
        string mstrFields_Detail = "HdrRecID,WorkerRecID,WorkerID,WorkPositionSeqNo,WKPriceType,UnitCost,Qty,Remark";

        public bool DeleteHdr(DBAccess dbcon, string strRecID)
        {
            DateTime datWorkDate = CvtFunc.ToDateTime(  dbcon.GetValue("Select WorkDate From rmWorkQtyInputHdr Where RecID='" + strRecID + "'"));

            DataFunctions.DeleteByRecID(dbcon, "rmWorkQtyInputHdr", strRecID);
            string strSQL = "Delete From rmWorkQtyInputDtl where HdrRecID='" + strRecID + "'";
            dbcon.ExecuteSql(strSQL);
            
            UpdateWorkerWorkSalary(dbcon, datWorkDate, strRecID);

            return true;
        }

        public bool AddDetail(DBAccess dbcon, DataRow drRow)
        {
            DataFunctions.UpdateTable(dbcon, mstrFields_Detail, "rmWorkQtyInputDtl", drRow, FormStatus.AddNew);
            return true;
        }

        public bool UpdateDetail(DBAccess dbcon, DataRow drRow)
        {
            DataFunctions.UpdateTable(dbcon, mstrFields_Detail, "rmWorkQtyInputDtl", drRow, FormStatus.Update);
            return true;
        }

        public bool UpdateDetailUnitCost(DBAccess dbcon,string strHdrRecID)
        {
            try
            {
                string strSQL = "";
                strSQL = @"select A.*,B.WorkingProcedureID from rmWorkQtyInputDtl A inner join rmWorkQtyInputHdr B on B.RecID=A.HdrRecID and B.RecID='{0}' and B.Post=0";
                strSQL = string.Format(strSQL, strHdrRecID);
                DataTable dtHdr = dbcon.GetDataTable(strSQL);
                foreach (DataRow drRow in dtHdr.Rows)
                {

                    strSQL = @"Update rmWorkQtyInputDtl set unitcost=B.WKUnitCost,WKPriceType=B.WKPriceType
                           From imCategoryWKWorkCostPosition B Where  B.WorkPositionSeqNo='{0}' and B.WKProductID='{1}' and rmWorkQtyInputDtl.RecID='{2}'";
                    strSQL = string.Format(strSQL, drRow["WorkPositionSeqNo"], drRow["WorkingProcedureID"], drRow["RecID"]);
                    dbcon.ExecuteSql(strSQL);

                }
                dbcon.ExecuteSql(strSQL);
                return true;
            }
            catch (Exception ex)
            {
                MsgBoxEx.Show(ex.ToString());
            }
            return false;
        }

        public bool Confirm(DBAccess dbcon ,long lngRecID)
        {
            string strSQL = "Update rmWorkQtyInputHdr set Confirmed =1 Where RecID='" + lngRecID + "'";
            dbcon.ExecuteSql(strSQL);
            return true;
        }

        public bool Posted(DBAccess dbcon, long lngRecID)
        {
            string strSQL = "Update rmWorkQtyInputHdr set Post =1,Confirmed =1 Where RecID='" + lngRecID + "'";
            dbcon.ExecuteSql(strSQL);
            return true;
        }

        public bool UpdateWorkerWorkSalary(DBAccess dbcon,DateTime datWorkDate,string strRecID)
        {
            string strStPrdDate = "";
            string strEdPrdDate = "";
            string strPrdYear = "";
            string strPrdMonth = "";
            string strSQL = "SELECT Period,StPrdDate,EnPrdDate FROM MPERIOD where StPrdDate<='{0}' and EnPrdDate>='{0}'";
            strSQL = string.Format(strSQL, datWorkDate);
            DataRow drPrd = dbcon.GetDataFirstRow(strSQL);
            {
                strPrdYear = CvtFunc.ToDateTime(drPrd["StPrdDate"]).Year.ToString();
                strPrdMonth = CvtFunc.ToString(drPrd["Period"]);

                strStPrdDate = CvtFunc.ToDateString(drPrd["StPrdDate"]);
                strEdPrdDate = CvtFunc.ToDateString(drPrd["EnPrdDate"]);
            }

            strSQL = "Delete From rmWorkerWorkSalary Where OrigHdrRecID='{2}'";
            strSQL = string.Format(strSQL, strPrdYear, strPrdMonth, strRecID);
            dbcon.ExecuteSql(strSQL);

            strSQL = @" INSERT INTO rmWorkerWorkSalary (PrdYear,PrdMonth,WorkDate,WorkerID,WorkSalary,OrigHdrRecID)
                        Select '{0}','{1}',WorkDate,WorkerID,sum(UnitCost*Qty),A.RecID from rmWorkQtyInputHdr A inner join rmWorkQtyInputDtl B on A.RecID=B.HdrRecID 
                        where A.confirmed=1 and A.WorkDate>='{2}' and A.WorkDate<='{3}' and a.RecID='{4}' group by WorkerID,A.RecID,A.WorkDate";
            strSQL = string.Format(strSQL, strPrdYear,strPrdMonth, strStPrdDate, strEdPrdDate, strRecID);
            dbcon.ExecuteSql(strSQL); 

            return true;
        }
    }
}
